Анализ продуктового портфеля электроинструмента¶

Описание проекта¶

За первое полугодие 2022 года падение рынка электроинструмента по сравнению с первым полугодием 2021 года составило 25%. В 2023 году ожидается продложение снижения рынка. В сявзи с этим необходимо подготовить предложения по развитию производства электроинструмента, а также повышение прибыльности изделий.

Цель проекта¶

Целью проекта является подготовка рекомендаций для повышения прибыли направления электроинструмента

Задачи проекта¶

  • подготовить матрицу рассеяния продуктового портфеля
  • определить "ценность" каждого изделия
  • определить топ-5 наиболее и наменее ценных изделий
  • подготовить рекомендации для развития напрвления электроинструмента

Подготовка данных¶

Импортируем библиотеки для работы с датафреймами и средств визуализации.

In [1]:
import pandas as pd
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

Откроем данные ABC-анализа (СМиП) и рентабельности (ФЭУ) электроинструмента и выведем на экран первые 5 строк.

In [2]:
abc = pd.read_excel(r'C:\Users\Геннадий Иовченко\Desktop\ABC\ABC.xlsx')
profit = pd.read_excel(r'C:\Users\Геннадий Иовченко\Desktop\ABC\profit.xlsx')
In [3]:
display(abc.head(), profit.head())
№ п/п Год освоения Наименование Количество Доля 9 мес, %
0 3 2007 Б1-30 5717 0.045917
1 6 2013 Б2-30 3869 0.031075
2 32 2019 Б2-30 МАСТЕР 710 0.005702
3 7 2014 Б3-40 3843 0.030866
4 24 2019 Б4-70 1306 0.010489
Название Рентабельность
0 Б1-30 7.718157
1 Б2-30 8.475530
2 Б2-30 МАСТЕР 11.512256
3 Б3-40 23.588806
4 Б4-70 12.719378

Предобработка данных¶

Для удобства работы переименуем столбцы в обоих датафреймах.

In [4]:
abc = abc.rename(
    columns={
        '№ п/п': 'number', 
        'Год освоения': 'year', 
        'Наименование': 'name', 
        'Количество': 'amount', 
        'Доля 9 мес, %': 'part'
    }
).drop('number', axis=1)
In [5]:
profit = profit.rename(
    columns={
        'Название': 'name', 
        'Рентабельность': 'profit'
    }
)

Переведём столбец с годом освоения в целочисленный формат.

In [6]:
abc['year'] = abc['year'].astype(int)

Переведём проценты в обоих датафреймах к единому виду.

In [7]:
abc['part'] = abc['part'] * 100

После того, как мы "причесали" наши данные, объединим их в единый датафрейм и отсортируем их по названию.

In [8]:
df = abc.merge(profit, on = 'name', how = 'inner').sort_values(by='name')

Добавим столбец с серией электроинструмента и типом электроинструмента.

In [9]:
df['series'] = 'Professional'
df.loc[abc['name'].str.contains('МАСТЕР'), 'series'] = 'Master'
In [10]:
df['type'] = 'бороздодел'
df.loc[df['name'].str.contains('МД'), 'type'] = 'миксер'
df.loc[df['name'].str.contains('МС'), 'type'] = 'дрель'
df.loc[df['name'].str.contains('МФ'), 'type'] = 'фреза'
df.loc[df['name'].str.contains('МШУ'), 'type'] = 'болгарка'
df.loc[df['name'].str.contains('П'), 'type'] = 'перфоратор'
df.loc[df['name'].str.contains('ПД'), 'type'] = 'пила'
df.loc[df['name'].str.contains('ПМ'), 'type'] = 'лобзик'
df.loc[df['name'].str.contains('ШВ'), 'type'] = 'шуруповёрт'

Приведём данные в удобный вид и округлим проценты. И посмотрим, что в итоге у нас получилось.

In [11]:
df = df[['year', 'name', 'type', 'series', 'amount', 'part', 'profit']]
df[['part', 'profit']] = round(df[['part', 'profit']], 2)
df.head()
Out[11]:
year name type series amount part profit
0 2007 Б1-30 бороздодел Professional 5717 4.59 7.72
1 2013 Б2-30 бороздодел Professional 3869 3.11 8.48
2 2019 Б2-30 МАСТЕР бороздодел Master 710 0.57 11.51
3 2014 Б3-40 бороздодел Professional 3843 3.09 23.59
4 2019 Б4-70 бороздодел Professional 1306 1.05 12.72

В итоге у нас получилась таблица с изделием, годом его освоения, типом изделия, серией, количеством проданных изделий, долей в портфеле заказа и рентабельностью.

Продуктовая матрица¶

Здесь и далее будут рассматриваться только изделия собственного производства

Распределение изделий¶

Построим матрицу рассения, где по оси X будет рентабельность, а по оси Y доля в портфеле продаж.

In [12]:
fig = px.scatter(df, 
                 y='part', x='profit', color='name', width=900, height=800, 
                 labels={
                     'part': 'Доля продаж (%)',
                     'profit': 'Рентабельность (%)',
                     'type': 'Тип изделия',
                     'name': 'Изделие'
                 },
                 title='Продуктовая матрица')

fig.add_hline(y=0, line_dash='dash')
fig.add_vline(x=0, line_dash='dash')
fig.update_layout(yaxis_title='Доля в портфеле заказов', 
                  xaxis_title='Рентабельность изделия')
fig.show()
  • Видим ярко выраженный объект - МД1-11Э, который и продаётся крайне хорошо (31,46%), и имеет достаточно высокую рентабельность.
  • Помимо МД1-11Э, только МФ3-1100Э преодолел 5%-ый барьер продаж, все остальные изделия находятся ниже.
  • Самая высокая рентабельность 23,59% у Б3-40, а минимальная -12,5% у П9-850-РЭ.
  • Заметен ярко выраженный кластер изделий с отрицательный рентабельностью и относительно высокими продажами.

Распределение типов изделий¶

Построим предыдущую матрицу с выделением не по конкретным изделиям, а по типам.

In [13]:
fig = px.scatter(df, 
                 y='part', x='profit', color='type', width=900, height=800,
                 labels={
                     'part': 'Доля продаж (%)',
                     'profit': 'Рентабельность (%)',
                     'type': 'Тип изделия',
                     'name': 'Изделие',
                     'year': 'Год'
                 },
                 title='Продуктовая матрица по типу изделия')

fig.add_hline(y=0, line_dash='dash')
fig.add_vline(x=0, line_dash='dash')
fig.update_layout(yaxis_title='Доля в портфеле заказов', 
                  xaxis_title='Рентабельность изделия')
fig.show()
  • У всех миксеров положительная рентабельность, свыше 10%, но МД2-7Э продаётся хуже своих "собратьев".
  • Все бороздоделы находятся в плюсе.
  • Если не считать грустного одинокого представителя линейки перфораторов П9-850-РЭ, то хуже всего обстоят дела у лобзиков. Только два новых представителя линейки ПМ6-700Э и ПМ7-720Э не убыточны (чуть выше 2%), но их доля меньше половины процента, остальные расположились в диапазоне -7..-11%, но в то же время достигают опасных (при такой рентабельности) 3,5% у ПМ5-720Э.
  • Дрели и болгарки расположились примерно одинаково по обе стороны прибыльности.

Распределение по годам¶

Построим ту же матрицу рассеяния с цветовым выделением годов освоения.

In [14]:
fig = px.scatter(df, 
                 y='part', x='profit', color='year', width=900, height=800,
                 labels={
                     'part': 'Доля продаж (%)',
                     'profit': 'Рентабельность (%)',
                     'type': 'Тип изделия',
                     'name': 'Изделие',
                     'year': 'Год'
                 },
                 title='Продуктовая матрица по годам')

fig.add_hline(y=0, line_dash='dash')
fig.add_vline(x=0, line_dash='dash')
fig.update_layout(yaxis_title='Доля в портфеле заказов', 
                  xaxis_title='Рентабельность изделия')
fig.show()
  • Сразу в глаза бросается, что новые изделия (более жёлтые) находятся на низком уровене продаж. Вероятнее всего, это вызвано недостаточным продвижением на рынке.
  • Много недавних изделий (2010-2020-х гг.) имеют положительную рентабельность, но низкие продажи.
  • Из "тревожных изделий" (убыточные, но популярные) из 2000-х гг.
  • Топовый МД1-11Э был освоен в 2004 г.

"Ценность" изделия¶

Чтобы было корректное понимание ценности продукта, необходимо ввести новую метрику value (англ. ценность), которая будет определяться как произведение спроса на рентабельность. Если на изделие высокий спрос и оно приносит много денег, то ценность такого изделия будет высокая, если один из показателей будет около нуля (не популярный продукт или не приносящий прибыль), то его ценность так же будет нулевая. Однако, при отрицательной рентабельности высокий спрос будет оказывать медвежью услугу - чем больше убыточных изделий продадим, тем в больший минус будем себя загонять.

Ценность измеряется в условных единицах, которая прямо не несёт физического смысла, однако весьма полезно при сравнении двух или более изделий. И для определения безубыточности при сравнения с 0.

Топ-10 по ценности¶

Выведем топ-10 ценных и топ-10 вредных изделий.

In [15]:
df['value'] = round(df['part'] * df['profit'], 2)
display(df.sort_values(by='value', ascending=False).head(10), df.sort_values(by='value', ascending=False).tail(10))
year name type series amount part profit value
6 2004 МД1-11Э миксер Professional 39173 31.46 15.35 482.91
18 1999 МФ3-1100Э фреза Professional 6988 5.61 13.81 77.47
3 2014 Б3-40 бороздодел Professional 3843 3.09 23.59 72.89
9 2014 МД3-12Э миксер Professional 3440 2.76 17.88 49.35
7 2011 МД1-11Э МАСТЕР миксер Professional 3050 2.45 16.85 41.28
0 2007 Б1-30 бороздодел Professional 5717 4.59 7.72 35.43
1 2013 Б2-30 бороздодел Professional 3869 3.11 8.48 26.37
4 2019 Б4-70 бороздодел Professional 1306 1.05 12.72 13.36
17 2009 МФ2-620Э фреза Master 1406 1.13 5.98 6.76
2 2019 Б2-30 МАСТЕР бороздодел Master 710 0.57 11.51 6.56
year name type series amount part profit value
19 2018 МФ4-1100Э фреза Master 2138 1.72 -5.73 -9.86
30 2003 МШУ2-9-125Э болгарка Professional 3374 2.71 -3.99 -10.81
40 2008 ПМ3-650Э лобзик Master 1712 1.38 -9.54 -13.17
29 2002 МШУ2-9-125 болгарка Professional 3183 2.56 -5.48 -14.03
43 2011 ПМ5-750Э МАСТЕР лобзик Professional 1773 1.42 -11.03 -15.66
12 2010 МСУ10-13-РЭ дрель Master 3940 3.16 -5.52 -17.44
31 2006 МШУ3-11-150 болгарка Master 2427 1.95 -9.33 -18.19
41 2005 ПМ4-700Э лобзик Master 3043 2.44 -7.74 -18.89
39 2003 ПМ3-600Э лобзик Professional 3235 2.60 -9.21 -23.95
42 2006 ПМ5-720Э лобзик Professional 4292 3.45 -8.19 -28.26
  • Абсолютно космических показателей достигает МД1-11Э, что было видно из продуктовой матрицы. Также в топе находятся другие миксеры, бороздоделы, а также фрезы.
  • Но фреза МФ4-1100Э забралась в анти-топ. На самом дне тройка лобзиков: 3, 4, 5. Также весьма популярная дрель МСУ10-13-РЭ.
  • Перфоратор П9-850-РЭ, несмотря на минимальную рентабельность, не попал в анти-топ из-за низкого спроса на него.

В дальнейшем для наглядности при визуализации исключаем МД1-11Э.

Ценность всех изделий¶

Построим горизонтальную столбчатую диаграмму для всех изделий.

In [16]:
fig = px.bar(df.query('value<100').sort_values(by='value'), 
             y='name', 
             x='value', 
             #color='type', 
             orientation='h',
             width=900,
             height=860,
             title='Ценность изделия',
             labels={
                 'value': 'Ценность',
                 'name': 'Изделие'
             }
            )
fig.show()

Ценность изделия по типу¶

Переформатируем исходные столбчатые диграммы с разбиением по типу изделий

In [17]:
fig = px.bar(df.query('value<100').sort_values(by='value'), 
             y='name', 
             x='value', 
             color='type', 
             orientation='h',
             width=900,
             height=860,
             title='Ценность изделия по типу',
             labels={
                 'value': 'Ценность',
                 'name': 'Изделие',
                 'type': 'Тип изделия'
             }
            )
fig.show()
  • Бороздоделы и миксера (держим в памяти МД1-11Э) показывают себя в значительной степени хорошо.
  • Фрезы имеют одно изделие с отрицательной ценностью, но в целом имеют положительный результат.
  • Хуже всего показывают себя лобзики и болгарки.

Убыточные и прибыльные изделия¶

Дополнительно выведем отдельно убыточные и прибыльные изделия.

In [18]:
df_low_value = df.query('value<0').sort_values(by='value')
df_high_value = df.query('0<value<100').sort_values(by='value')

fig = make_subplots(rows=2, cols=1)

fig.append_trace(go.Bar(
    y=df_low_value['name'], 
    x=df_low_value['value'],
    orientation='h',
    name='Убыточные',
), 1, 1)

fig.append_trace(go.Bar(
    y=df_high_value['name'], 
    x=df_high_value['value'],
    orientation='h',
    name='Прибыльные',
), 2, 1)

fig.update_layout(
    title='Вклад убыточных и прибыльных изделий',
    height=1100
)

Общие рекомендации:¶

  • МД1-11Э - топовое изделие, но находиться в зависимости от одного изделия достаточно рисковано. Необходимо иметь хотя бы три изделия со сравнимой ценностью.
  • Определить технические тенденции для объяснения общей низкой ценности лобзиков и болгарок.
  • Повысить цену на лобзики ПМ3-600Э, ПМ4-700Э, ПМ5-720Э, что позволит снизить спрос и повысить рентабельность изделий, что, в свою очередь, позволит кратно повысить ценность.
  • На изделия с высокой рентабельностью (Б3-40, Б4-70, МД3-12Э) рассмотреть снижение цены по некоторым каналам сбыта, чтобы понять эластичность спроса, возможно через объявление скидочных кампаний. На основании полученных результатов определить дальнейший путь для данных изделий. Второе изделие по спросу, МФ3-1100Э, является вторым и по ценности. Возможно ли за счёт запасов рентабельности поднять спрос?
  • Учитывая низкие показатели продаж у новых изделий, более активно поддерживать продвижение новых изделий на рынок.
  • Рассмотреть возможность освоения высокорентабельных, но нишевых изделий, так называемой "техники специального назначения".

Дашборд¶

Сохраним итоговый датафрейм в формате xlsx для возможности дальнейшей работы.

In [19]:
df.to_excel(r'C:\Users\Геннадий Иовченко\Desktop\ABC\product_matrix.xlsx')